package com.keshe.util;



import com.keshe.domain.Room;
import com.keshe.domain.User;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DBUtil {


    //数据库用户名
    private static final String USERNAME = "root";
    //数据库密码
    private static final String PASSWORD = "Wzh201227";
    //驱动信息
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    //数据库地址
    private static final String URL = "jdbc:mysql://localhost:3306/hotel";

    static{
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection(){
        Connection connection  = null;
        try {
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return connection;
    }
    public static void close(Connection connection,PreparedStatement pstmt,ResultSet resultSet){
        try {

            if(resultSet!=null){
                resultSet.close();
            }

            if(pstmt!=null){
                pstmt.close();
            }
            if(connection!=null){
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /*
           将查询到的结果集转换成自定义类的对象，并存在在List中
    <T>声明此方法持有一个类型T，此方法为一个泛型方法
    Class<T> cla指明泛型T的具体类型
    params是一个Object类型的数组
     */
    public static <T> List<T> query(String sql, Class<T> cla, Object... params){
        Connection conn = DBUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<T> list = null;
        try{
            //预编译
            ps = conn.prepareStatement(sql);
            if(params != null){
                for(int i = 0; i < params.length; ++i){
                    //替换指定位置的占位符
                    ps.setObject(i+1, params[i]);
                }
            }
            //执行查询
            rs = ps.executeQuery();
            //获取描述结果集基础信息（列数、数据类型、字段）的meta元数据
            ResultSetMetaData rsd = rs.getMetaData();
            //返回值
            list = new ArrayList<T>();
            while (rs.next()){
                //实例化一个泛型类
                //T t = cla.newInstance();
                T t = cla.getDeclaredConstructor().newInstance(); // 创建对象
                for (int i = 0; i < rsd.getColumnCount(); ++i){
                    //获取列名，sql语句中有别名返回别名，否则返回值与getColumnName()方法相同
                    String columnName = rsd.getColumnLabel(i+1);
                    //根据列名取值
                    Object columnValue = rs.getObject(columnName);
                    //根据索引取值
                    //Object value = rs.getObject(i+1);
                    //根据列名获取属性
                    Field field;
                    field = cla.getDeclaredField(convertFormat(columnName));

                    //开启私有变量访问权限
                    field.setAccessible(true);
                    //给对象t的变量赋值
                    field.set(t, columnValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }finally {
            // 关闭资源
            DBUtil.close(conn, ps, rs);
        }
    }

    public static String convertFormat(String source){
        String[] strings = source.split("_");
        StringBuffer sf = new StringBuffer(strings[0]);
        for(int i = 1; i < strings.length; ++i){
            sf.append(strings[i].substring(0,1).toUpperCase() + strings[i].substring(1));
        }
        return sf.toString();
    }


    /**
     * 对任意表的一个非查询
     *
     * @param sql
     * @param objects
     * @return
     */
    public static int update(String sql, Object... objects) {
        Connection conn = DBUtil.getConnection();
        PreparedStatement ps = null;
        int i = 0;
        try {
            // 预编译
            ps = conn.prepareStatement(sql);
            // 给sql赋值
            for (int j = 0; j < objects.length; j++) {
                ps.setObject(j + 1, objects[j]);
            }
            // 执行
            i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            DBUtil.close(conn, ps, null);
        }
        return i;
    }

    public static void main(String[] args) {
        String sql="select * from user";
        List<User> roomList=DBUtil.query(sql, User.class);
        for(User r:roomList){
            System.out.println(r);
        }
//        String sql1="delete from Room where roomid=?";
//        int i=DBUtil.update(sql1,4);
//        System.out.println(i);
    }
}





